﻿using DiaoPaiDaYin;
using DXApplicationTangche.DTO;
using DXApplicationTangche.UC.款式异常;
using DXApplicationTangche.UC.门店下单.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static DXApplicationTangche.原型.TaskDTOS;

namespace DXApplicationTangche.service
{
    /// <summary>
    /// 面料服务
    /// </summary>
    public class FabricService
    {

        public static List<面料导入DTO> get面料导入DTOByCodes(List<String> MATERIAL_CODEs) {
            String sql = "SELECT\n" +
                "	MATERIAL_ID,\n" +
                "	MATERIAL_CODE,\n" +
                "	MATERIAL_CATEGORY,\n" +
                "	MATERIAL_NAME_CN,\n" +
                "	MATERIAL_SPEC,\n" +
                "	MATERIAL_UNIT_CD,\n" +
                "	MATERIAL_GRAM_WEIGHT,\n" +
                "	MATERIAL_YEAR,\n" +
                "	MATERIAL_SEASON,\n" +
                "	MATERIAL_COMPOSITION,\n" +
                "	MATERIAL_WEAVE,\n" +
                "	MATERIAL_COLOR,\n" +
                "	MATERIAL_STYLE,\n" +
                "	MATERIAL_THREAD_COUNT,\n" +
                "	MATERIAL_OTHER1,\n" +
                "	MATERIAL_LEVEL,\n" +
                "	MATERIAL_OTHER2,\n" +
                "	MATERIAL_TYPE_CD \n" +
                "FROM\n" +
                "	i_material_p \n" +
                "WHERE\n" +
                "	MATERIAL_CODE IN ( '"+ String.Join("','", MATERIAL_CODEs) + "' );";
            DataTable dataTable = SQLmtm.GetDataTable(sql);

            List<面料导入DTO> 面料导入DTOs = new List<面料导入DTO>();
            foreach (DataRow dataRow in dataTable.Rows) {
                面料导入DTOs.Add(new 面料导入DTO(dataRow));
            }
            return 面料导入DTOs;
        }

        public static List<面料DTO> get面料DTOs(List<String> SYTLE_FABRIC_ID)
        {
            String sql = "SELECT\n" +
                "	id,\n" +
                "	fileId,\n" +
                "	materialNameCn,\n" +
                "	materialNameEn,\n" +
                "	materialCode,\n" +
                "	materialUseType,\n" +
                "	materialTypeCd,\n" +
                "	materialComposition,\n" +
                "	materialSpec,\n" +
                "	materialUnitCd,\n" +
                "	materialYear,\n" +
                "	materialColor,\n" +
                "	materialSeason,\n" +
                "	remarks,\n" +
                "	materialStyleCategory,\n" +
                "	modelFilepath,\n" +
                "	filePath,\n" +
                "	createby_lastName,\n" +
                "	createby_firstName,\n" +
                "	materialPrice_materialSalePrice,\n" +
                "	inventoryMaterial_materialQuantity,\n" +
                "	materialLevel,\n" +
                "	materialStyle,\n" +
                "	materialWeave,\n" +
                "	materiaFacPrice,\n" +
                "	mianliao,\n" +
                "	picn,\n" +
                "	picurl \n" +
                "FROM\n" +
                "	V_MATERIAL_CATEGORY_Fabric \n" +
                "WHERE\n" +
                "	id IN ( '" + String.Join("','", SYTLE_FABRIC_ID) + "' );";
            DataTable dt = SQLmtm.GetDataTable(sql);
            List<面料DTO> 面料DTOs = new List<面料DTO>();
            foreach (DataRow dr in dt.Rows)
            {
                面料DTOs.Add(new 面料DTO(dr));
            }
            return 面料DTOs;
        }


        /// <summary>
        /// 所有面料
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAllMaterial()
        {
            String sql = "SELECT id,materialNameCn,materialCode,materialComposition FROM v_material_category_fabric";
            return SQLmtm.GetDataTable(sql);
        }
        /// <summary>
        /// 所有面料for样品下单
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAll面料()
        {
            //            String sql = "SELECT\n" +
            //"	id AS ID,\n" +
            //"	materialNameCn AS `Name`,\n" +
            //"	materialCode AS `Code`,\n" +
            //"	materialComposition,\n" +
            //"	materialSpec \n" +
            //"FROM\n" +
            //"	v_material_category_fabric;";
            String sql = "SELECT\n" +
            "	MATERIAL_ID AS ID,\n" +
            "	MATERIAL_NAME_CN AS `Name`,\n" +
            "	MATERIAL_CODE AS `Code`,\n" +
            "	MATERIAL_COMPOSITION,\n" +
            "	MATERIAL_SPEC \n" +
            "FROM\n" +
            "	`i_material_p` \n" +
            "WHERE\n" +
            "	MATERIAL_CATEGORY = 'MATERIAL_CATEGORY-Fabric';";
            return SQLmtm.GetDataTable(sql);
        }
        /// <summary>
        /// 根据id取面料信息
        /// </summary>
        /// <param name="SYTLE_FABRIC_ID"></param>
        /// <returns></returns>
        public static DataRow generateMaterialInfo(String SYTLE_FABRIC_ID)
        {
            String sql = "SELECT\n" +
                "	MATERIAL_ID,\n" +
                "	MATERIAL_NAME_CN,\n" +
                "	MATERIAL_COLOR \n" +
                "FROM\n" +
                "	i_material_p \n" +
                "WHERE\n" +
                "	MATERIAL_ID = '" + SYTLE_FABRIC_ID + "'";
            return SQLmtm.GetDataRow(sql);
        }

        /// <summary>
        /// 查询面料
        /// </summary>
        /// <param name="str"></param>
        /// <param name="page"></param>
        /// <returns></returns>
        public static List<面料图片Dto> GetMianLiao(String str, int page)
        {
            String sql = "SELECT\n" +
                "	*,CONCAT(s1.materialCode,':',s1.materialNameCn) AS mianliao,\n" +
                "SUBSTRING_INDEX( s1.filePath, '/',- 1 ) AS picn,\n" +
                "	CONCAT( 'https://sshirtmtmbucket.oss-cn-zhangjiakou.aliyuncs.com/sshirtmtm/', SUBSTRING_INDEX( s1.filePath, '/',- 1 ) ) AS picurl \n" +
                "FROM\n" +
                "	(\n" +
                "	SELECT\n" +
                "		a.material_id AS \"id\",\n" +
                "		a.material_file_id AS \"fileId\",\n" +
                "		a.material_name_cn AS \"materialNameCn\",\n" +
                "		a.material_name_en AS \"materialNameEn\",\n" +
                "		a.material_code AS \"materialCode\",\n" +
                "		a.material_use_type AS \"materialUseType\",\n" +
                "		a.material_type_cd AS \"materialTypeCd\",\n" +
                "		a.material_composition AS \"materialComposition\",\n" +
                "		a.material_spec AS \"materialSpec\",\n" +
                "		a.material_unit_cd AS \"materialUnitCd\",\n" +
                "		a.material_year AS \"materialYear\",\n" +
                "		a.material_color AS \"materialColor\",\n" +
                "		a.material_season AS \"materialSeason\",\n" +
                "		a.remarks AS \"remarks\",\n" +
                "		a.material_category AS \"materialStyleCategory\",\n" +
                "		a.model_filepath AS \"modelFilepath\",\n" +
                "	CASE\n" +
                "			\n" +
                "			WHEN file.FILE_PATH IS NOT NULL \n" +
                "			AND file.FILE_PATH != '' THEN\n" +
                "				REPLACE ( CONCAT( file.FILE_PATH, file.UPLOAD_FILE ), 'fragsmart-erp', 'fragsmart-mtm' ) ELSE REPLACE ( CONCAT( upload_file.FILE_PATH, upload_file.UPLOAD_FILE ), 'fragsmart-erp', 'fragsmart-mtm' ) \n" +
                "				END AS filePath,\n" +
                "			a_login_user_p.LAST_NAME AS \"createby_lastName\",\n" +
                "			a_login_user_p.FIRST_NAME AS \"createby_firstName\",\n" +
                "			IFNULL( s.material_sale_price, 0 ) AS \"materialPrice.materialSalePrice\",\n" +
                "			IFNULL( inventory_material.MATERIAL_QUANTITY, 0 ) AS \"inventoryMaterial.materialQuantity\",\n" +
                "			a.MATERIAL_LEVEL AS \"materialLevel\",\n" +
                "			a.MATERIAL_STYLE AS \"materialStyle\",\n" +
                "			a.MATERIAL_WEAVE AS \"materialWeave\",\n" +
                "		CASE\n" +
                "				\n" +
                "				WHEN adp.ITEM_VALUE = \"A_150\" THEN\n" +
                "				150 \n" +
                "				WHEN adp.ITEM_VALUE = \"B_180\" THEN\n" +
                "				180 \n" +
                "				WHEN adp.ITEM_VALUE = \"C_240\" THEN\n" +
                "				240 \n" +
                "				WHEN adp.ITEM_VALUE = \"D_320\" THEN\n" +
                "				320 ELSE 0 \n" +
                "			END AS \"materiaFacPrice\" \n" +
                "		FROM\n" +
                "			i_material_p a\n" +
                "			LEFT JOIN a_shop_material_r ar ON a.MATERIAL_ID = ar.MATERIAL_ID\n" +
                "			LEFT JOIN a_login_user_p a_login_user_p ON a_login_user_p.login_user_id = a.create_user\n" +
                "			LEFT JOIN i_material_price_s s ON s.material_id = a.material_id \n" +
                "			AND s.SHOP_ID = 18\n" +
                "			INNER JOIN i_inventory_material_p inventory_material ON inventory_material.material_id = a.material_id\n" +
                "			LEFT JOIN w_upload_file_p file ON a.material_file_id = file.FILE_ID \n" +
                "			AND file.FTP_FILE = \"/material\"\n" +
                "			LEFT JOIN a_upload_file_p upload_file ON upload_file.FILE_ID = a.MATERIAL_FILE_ID \n" +
                "			AND upload_file.FILE_KBN = 0 \n" +
                "			AND upload_file.FTP_FILE = \"/material\"\n" +
                "			LEFT JOIN a_dict_p adp ON a.MATERIAL_LEVEL = CONCAT( adp.ITEM_CD, \"-\", adp.ITEM_VALUE ) \n" +
                "		WHERE\n" +
                "			a.delete_flag = 0 \n" +
                "			AND a.material_category IN ( 'MATERIAL_CATEGORY-Fabric', 'MATERIAL_CATEGORY-ButtonL', 'MATERIAL_CATEGORY-Suit_Fabric', 'MATERIAL_CATEGORY-Suit_Material' ) \n" +
                "			AND ar.SHOP_ID = 18 \n" +
                "			AND ( a.material_name_cn LIKE '%" + str + "%' OR a.material_code LIKE '%" + str + "%' ) \n" +
                "           AND a.material_category ='MATERIAL_CATEGORY-Fabric'" +
                "		ORDER BY\n" +
                "			a.MATERIAL_CATEGORY,\n" +
                "			a.MATERIAL_ID \n" +
                " LIMIT " + ((page - 1) * 36).ToString() + ",36" +
                "	) AS s1";
            DataTable dt = SQLmtm.GetDataTable(sql);
            List<面料图片Dto> 面料图片dtos = new List<面料图片Dto>();
            foreach (DataRow dr in dt.Rows)
            {
                面料图片dtos.Add(new 面料图片Dto(dr));
            }
            return 面料图片dtos;
        }

        /// <summary>
        /// 查询默认面料
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>          
        public static List<面料图片Dto> DefaultMianLiao(String id, String str, int page)
        {
            String sql = "SELECT\n" +
                "	ap.ITEM_CD AS 'materialCode',\n" +
                "	ap.ITEM_VALUE AS 'id',\n" +
                "	ap.ITEM_NAME_CN AS 'mianliao',\n" +
                "	SUBSTRING_INDEX( s1.filePath, '/',- 1 ) AS picn,\n" +
                "	CONCAT( 'https://sshirtmtmbucket.oss-cn-zhangjiakou.aliyuncs.com/sshirtmtm/', SUBSTRING_INDEX( s1.filePath, '/',- 1 ) ) AS picurl \n" +
                "FROM\n" +
                "	a_kuanshi_p AS ap\n" +
                "	LEFT JOIN (\n" +
                "	SELECT\n" +
                "		a.material_id AS \"id\",\n" +
                "		a.material_file_id AS \"fileId\",\n" +
                "		a.material_name_cn AS \"materialNameCn\",\n" +
                "		a.material_name_en AS \"materialNameEn\",\n" +
                "		a.material_code AS \"materialCode\",\n" +
                "		a.material_use_type AS \"materialUseType\",\n" +
                "		a.material_type_cd AS \"materialTypeCd\",\n" +
                "		a.material_composition AS \"materialComposition\",\n" +
                "		a.material_spec AS \"materialSpec\",\n" +
                "		a.material_unit_cd AS \"materialUnitCd\",\n" +
                "		a.material_year AS \"materialYear\",\n" +
                "		a.material_color AS \"materialColor\",\n" +
                "		a.material_season AS \"materialSeason\",\n" +
                "		a.remarks AS \"remarks\",\n" +
                "		a.material_category AS \"materialStyleCategory\",\n" +
                "		a.model_filepath AS \"modelFilepath\",\n" +
                "	CASE\n" +
                "			\n" +
                "			WHEN file.FILE_PATH IS NOT NULL \n" +
                "			AND file.FILE_PATH != '' THEN\n" +
                "				REPLACE ( CONCAT( file.FILE_PATH, file.UPLOAD_FILE ), 'fragsmart-erp', 'fragsmart-mtm' ) ELSE REPLACE ( CONCAT( upload_file.FILE_PATH, upload_file.UPLOAD_FILE ), 'fragsmart-erp', 'fragsmart-mtm' ) \n" +
                "				END AS filePath,\n" +
                "			a_login_user_p.LAST_NAME AS \"createby.lastName\",\n" +
                "			a_login_user_p.FIRST_NAME AS \"createby.firstName\",\n" +
                "			IFNULL( s.material_sale_price, 0 ) AS \"materialPrice.materialSalePrice\",\n" +
                "			IFNULL( inventory_material.MATERIAL_QUANTITY, 0 ) AS \"inventoryMaterial.materialQuantity\",\n" +
                "			a.MATERIAL_LEVEL AS \"materialLevel\",\n" +
                "			a.MATERIAL_STYLE AS \"materialStyle\",\n" +
                "			a.MATERIAL_WEAVE AS \"materialWeave\",\n" +
                "		CASE\n" +
                "				\n" +
                "				WHEN adp.ITEM_VALUE = \"A_150\" THEN\n" +
                "				150 \n" +
                "				WHEN adp.ITEM_VALUE = \"B_180\" THEN\n" +
                "				180 \n" +
                "				WHEN adp.ITEM_VALUE = \"C_240\" THEN\n" +
                "				240 \n" +
                "				WHEN adp.ITEM_VALUE = \"D_320\" THEN\n" +
                "				320 ELSE 0 \n" +
                "			END AS \"materiaFacPrice\" \n" +
                "		FROM\n" +
                "			i_material_p a\n" +
                "			LEFT JOIN a_shop_material_r ar ON a.MATERIAL_ID = ar.MATERIAL_ID\n" +
                "			LEFT JOIN a_login_user_p a_login_user_p ON a_login_user_p.login_user_id = a.create_user\n" +
                "			LEFT JOIN i_material_price_s s ON s.material_id = a.material_id \n" +
                "			AND s.SHOP_ID = 18\n" +
                "			INNER JOIN i_inventory_material_p inventory_material ON inventory_material.material_id = a.material_id\n" +
                "			LEFT JOIN w_upload_file_p file ON a.material_file_id = file.FILE_ID \n" +
                "			AND file.FTP_FILE = \"/material\"\n" +
                "			LEFT JOIN a_upload_file_p upload_file ON upload_file.FILE_ID = a.MATERIAL_FILE_ID \n" +
                "			AND upload_file.FILE_KBN = 0 \n" +
                "			AND upload_file.FTP_FILE = \"/material\"\n" +
                "			LEFT JOIN a_dict_p adp ON a.MATERIAL_LEVEL = CONCAT( adp.ITEM_CD, \"-\", adp.ITEM_VALUE ) \n" +
                "		WHERE\n" +
                "			a.delete_flag = 0 \n" +
                "			AND a.material_category IN ( 'MATERIAL_CATEGORY-Fabric', 'MATERIAL_CATEGORY-ButtonL', 'MATERIAL_CATEGORY-Suit_Fabric', 'MATERIAL_CATEGORY-Suit_Material' ) \n" +
                "			AND ar.SHOP_ID = 18 \n" +
                "			AND ( a.material_name_cn LIKE '%" + str + "%' OR a.material_code LIKE '%" + str + "%' ) \n" +
                "			AND a.material_category = 'MATERIAL_CATEGORY-Fabric' \n" +
                "		ORDER BY\n" +
                "			a.MATERIAL_CATEGORY,\n" +
                "			a.MATERIAL_ID \n" +
                "		) AS s1 ON ap.ITEM_VALUE = s1.id \n" +
                "	WHERE\n" +
                "	ap.PARENT_ID IN ( SELECT id FROM a_kuanshi_p WHERE STYLEID = '" + id + "' AND ITEM_VALUE = 'mianliaoid' ) \n" +
                "	AND ITEM_NAME_CN LIKE '%" + str + "%' " +
                " LIMIT " + ((page - 1) * 36).ToString() + ",36";
            DataTable dt = SQLmtm.GetDataTable(sql);
            List<面料图片Dto> 面料图片dtos = new List<面料图片Dto>();
            foreach (DataRow dr in dt.Rows)
            {
                面料图片dtos.Add(new 面料图片Dto(dr));
            }
            return 面料图片dtos;
        }

        /// <summary>
        /// 获取面料图片路径
        /// </summary>
        /// <param name="mlid"></param>
        /// <returns></returns>
        public static String GetMianLiaoFilePath(String mlid)
        {
            String sql = "SELECT\n" +
                "	*,CONCAT(s1.materialCode,':',s1.materialNameCn) AS mianliao,\n" +
                "SUBSTRING_INDEX( s1.filePath, '/',- 1 ) AS picn,\n" +
                "	CONCAT( 'https://sshirtmtmbucket.oss-cn-zhangjiakou.aliyuncs.com/sshirtmtm/', SUBSTRING_INDEX( s1.filePath, '/',- 1 ) ) AS picurl \n" +
                "FROM\n" +
                "	(\n" +
                "	SELECT\n" +
                "		a.material_id AS \"id\",\n" +
                "		a.material_file_id AS \"fileId\",\n" +
                "		a.material_name_cn AS \"materialNameCn\",\n" +
                "		a.material_name_en AS \"materialNameEn\",\n" +
                "		a.material_code AS \"materialCode\",\n" +
                "		a.material_use_type AS \"materialUseType\",\n" +
                "		a.material_type_cd AS \"materialTypeCd\",\n" +
                "		a.material_composition AS \"materialComposition\",\n" +
                "		a.material_spec AS \"materialSpec\",\n" +
                "		a.material_unit_cd AS \"materialUnitCd\",\n" +
                "		a.material_year AS \"materialYear\",\n" +
                "		a.material_color AS \"materialColor\",\n" +
                "		a.material_season AS \"materialSeason\",\n" +
                "		a.remarks AS \"remarks\",\n" +
                "		a.material_category AS \"materialStyleCategory\",\n" +
                "		a.model_filepath AS \"modelFilepath\",\n" +
                "	CASE\n" +
                "			\n" +
                "			WHEN file.FILE_PATH IS NOT NULL \n" +
                "			AND file.FILE_PATH != '' THEN\n" +
                "				REPLACE ( CONCAT( file.FILE_PATH, file.UPLOAD_FILE ), 'fragsmart-erp', 'fragsmart-mtm' ) ELSE REPLACE ( CONCAT( upload_file.FILE_PATH, upload_file.UPLOAD_FILE ), 'fragsmart-erp', 'fragsmart-mtm' ) \n" +
                "				END AS filePath,\n" +
                "			a_login_user_p.LAST_NAME AS \"createby.lastName\",\n" +
                "			a_login_user_p.FIRST_NAME AS \"createby.firstName\",\n" +
                "			IFNULL( s.material_sale_price, 0 ) AS \"materialPrice.materialSalePrice\",\n" +
                "			IFNULL( inventory_material.MATERIAL_QUANTITY, 0 ) AS \"inventoryMaterial.materialQuantity\",\n" +
                "			a.MATERIAL_LEVEL AS \"materialLevel\",\n" +
                "			a.MATERIAL_STYLE AS \"materialStyle\",\n" +
                "			a.MATERIAL_WEAVE AS \"materialWeave\",\n" +
                "		CASE\n" +
                "				\n" +
                "				WHEN adp.ITEM_VALUE = \"A_150\" THEN\n" +
                "				150 \n" +
                "				WHEN adp.ITEM_VALUE = \"B_180\" THEN\n" +
                "				180 \n" +
                "				WHEN adp.ITEM_VALUE = \"C_240\" THEN\n" +
                "				240 \n" +
                "				WHEN adp.ITEM_VALUE = \"D_320\" THEN\n" +
                "				320 ELSE 0 \n" +
                "			END AS \"materiaFacPrice\" \n" +
                "		FROM\n" +
                "			i_material_p a\n" +
                "			LEFT JOIN a_shop_material_r ar ON a.MATERIAL_ID = ar.MATERIAL_ID\n" +
                "			LEFT JOIN a_login_user_p a_login_user_p ON a_login_user_p.login_user_id = a.create_user\n" +
                "			LEFT JOIN i_material_price_s s ON s.material_id = a.material_id \n" +
                "			AND s.SHOP_ID = 18\n" +
                "			INNER JOIN i_inventory_material_p inventory_material ON inventory_material.material_id = a.material_id\n" +
                "			LEFT JOIN w_upload_file_p file ON a.material_file_id = file.FILE_ID \n" +
                "			AND file.FTP_FILE = \"/material\"\n" +
                "			LEFT JOIN a_upload_file_p upload_file ON upload_file.FILE_ID = a.MATERIAL_FILE_ID \n" +
                "			AND upload_file.FILE_KBN = 0 \n" +
                "			AND upload_file.FTP_FILE = \"/material\"\n" +
                "			LEFT JOIN a_dict_p adp ON a.MATERIAL_LEVEL = CONCAT( adp.ITEM_CD, \"-\", adp.ITEM_VALUE ) \n" +
                "		WHERE\n" +
                "			a.delete_flag = 0 \n" +
                "			AND a.material_category IN ( 'MATERIAL_CATEGORY-Fabric', 'MATERIAL_CATEGORY-ButtonL', 'MATERIAL_CATEGORY-Suit_Fabric', 'MATERIAL_CATEGORY-Suit_Material' ) \n" +
                "			AND ar.SHOP_ID = 18  AND a.MATERIAL_ID ='" + mlid + "'\n" +
                //"			AND ( a.material_name_cn LIKE '%" + mlname + "%' OR a.material_code LIKE '%" + mlname + "%' ) \n" +
                "           AND a.material_category ='MATERIAL_CATEGORY-Fabric'" +
                "		ORDER BY\n" +
                "			a.MATERIAL_CATEGORY,\n" +
                "			a.MATERIAL_ID \n" +
                "	) AS s1";

            return @"pic\" + SQLmtm.GetDataRow(sql)["picn"].ToString().Trim();
            //SQLmtm.GetDataRow(sql)["picn"].ToString();
        }

        /// <summary>
        /// 获取面料图片
        /// </summary>
        /// <param name="mlid"></param>
        /// <returns></returns>
        public static Image GetMianLiaoFile(String mlid)
        {
            return Image.FromFile(GetMianLiaoFilePath(mlid));
        }

        /// <summary>
        /// 根据面料id查询面料CD
        /// </summary>
        /// <param name="mlid"></param>
        /// <returns></returns>
        public static String GetMianLiaoCD(String mlid, String tab)
        {
            String sql = "SELECT\n" +
                "	*,CONCAT(s1.materialCode,':',s1.materialNameCn) AS mianliao,\n" +
                "SUBSTRING_INDEX( s1.filePath, '/',- 1 ) AS picn,s1.materialCode,s1.materialComposition,\n" +
                "	CONCAT( 'https://sshirtmtmbucket.oss-cn-zhangjiakou.aliyuncs.com/sshirtmtm/', SUBSTRING_INDEX( s1.filePath, '/',- 1 ) ) AS picurl \n" +
                "FROM\n" +
                "	(\n" +
                "	SELECT\n" +
                "		a.material_id AS \"id\",\n" +
                "		a.material_file_id AS \"fileId\",\n" +
                "		a.material_name_cn AS \"materialNameCn\",\n" +
                "		a.material_name_en AS \"materialNameEn\",\n" +
                "		a.material_code AS \"materialCode\",\n" +
                "		a.material_use_type AS \"materialUseType\",\n" +
                "		a.material_type_cd AS \"materialTypeCd\",\n" +
                "		a.material_composition AS \"materialComposition\",\n" +
                "		a.material_spec AS \"materialSpec\",\n" +
                "		a.material_unit_cd AS \"materialUnitCd\",\n" +
                "		a.material_year AS \"materialYear\",\n" +
                "		a.material_color AS \"materialColor\",\n" +
                "		a.material_season AS \"materialSeason\",\n" +
                "		a.remarks AS \"remarks\",\n" +
                "		a.material_category AS \"materialStyleCategory\",\n" +
                "		a.model_filepath AS \"modelFilepath\",\n" +
                "	CASE\n" +
                "			\n" +
                "			WHEN file.FILE_PATH IS NOT NULL \n" +
                "			AND file.FILE_PATH != '' THEN\n" +
                "				REPLACE ( CONCAT( file.FILE_PATH, file.UPLOAD_FILE ), 'fragsmart-erp', 'fragsmart-mtm' ) ELSE REPLACE ( CONCAT( upload_file.FILE_PATH, upload_file.UPLOAD_FILE ), 'fragsmart-erp', 'fragsmart-mtm' ) \n" +
                "				END AS filePath,\n" +
                "			a_login_user_p.LAST_NAME AS \"createby.lastName\",\n" +
                "			a_login_user_p.FIRST_NAME AS \"createby.firstName\",\n" +
                "			IFNULL( s.material_sale_price, 0 ) AS \"materialPrice.materialSalePrice\",\n" +
                "			IFNULL( inventory_material.MATERIAL_QUANTITY, 0 ) AS \"inventoryMaterial.materialQuantity\",\n" +
                "			a.MATERIAL_LEVEL AS \"materialLevel\",\n" +
                "			a.MATERIAL_STYLE AS \"materialStyle\",\n" +
                "			a.MATERIAL_WEAVE AS \"materialWeave\",\n" +
                "		CASE\n" +
                "				\n" +
                "				WHEN adp.ITEM_VALUE = \"A_150\" THEN\n" +
                "				150 \n" +
                "				WHEN adp.ITEM_VALUE = \"B_180\" THEN\n" +
                "				180 \n" +
                "				WHEN adp.ITEM_VALUE = \"C_240\" THEN\n" +
                "				240 \n" +
                "				WHEN adp.ITEM_VALUE = \"D_320\" THEN\n" +
                "				320 ELSE 0 \n" +
                "			END AS \"materiaFacPrice\" \n" +
                "		FROM\n" +
                "			i_material_p a\n" +
                "			LEFT JOIN a_shop_material_r ar ON a.MATERIAL_ID = ar.MATERIAL_ID\n" +
                "			LEFT JOIN a_login_user_p a_login_user_p ON a_login_user_p.login_user_id = a.create_user\n" +
                "			LEFT JOIN i_material_price_s s ON s.material_id = a.material_id \n" +
                "			AND s.SHOP_ID = 18\n" +
                "			INNER JOIN i_inventory_material_p inventory_material ON inventory_material.material_id = a.material_id\n" +
                "			LEFT JOIN w_upload_file_p file ON a.material_file_id = file.FILE_ID \n" +
                "			AND file.FTP_FILE = \"/material\"\n" +
                "			LEFT JOIN a_upload_file_p upload_file ON upload_file.FILE_ID = a.MATERIAL_FILE_ID \n" +
                "			AND upload_file.FILE_KBN = 0 \n" +
                "			AND upload_file.FTP_FILE = \"/material\"\n" +
                "			LEFT JOIN a_dict_p adp ON a.MATERIAL_LEVEL = CONCAT( adp.ITEM_CD, \"-\", adp.ITEM_VALUE ) \n" +
                "		WHERE\n" +
                "			a.delete_flag = 0 \n" +
                "			AND a.material_category IN ( 'MATERIAL_CATEGORY-Fabric', 'MATERIAL_CATEGORY-ButtonL', 'MATERIAL_CATEGORY-Suit_Fabric', 'MATERIAL_CATEGORY-Suit_Material' ) \n" +
                "			AND ar.SHOP_ID = 18  AND a.MATERIAL_ID ='" + mlid + "'\n" +
                //"			AND ( a.material_name_cn LIKE '%" + mlname + "%' OR a.material_code LIKE '%" + mlname + "%' ) \n" +
                "           AND a.material_category ='MATERIAL_CATEGORY-Fabric'" +
                "		ORDER BY\n" +
                "			a.MATERIAL_CATEGORY,\n" +
                "			a.MATERIAL_ID \n" +
                "	) AS s1";
            if (tab == "cd")
            {
                return SQLmtm.GetDataRow(sql)["materialCode"].ToString();
            }
            else
            {
                return SQLmtm.GetDataRow(sql)["materialComposition"].ToString();
            }
        }

        public static DataRow GetMianLiaoDR(String id)
        {
            String sql = "SELECT\n" +
                "	*,CONCAT(s1.materialCode,':',s1.materialNameCn) AS mianliao,\n" +
                "SUBSTRING_INDEX( s1.filePath, '/',- 1 ) AS picn,s1.materialCode,s1.materialComposition,\n" +
                "	CONCAT( 'https://sshirtmtmbucket.oss-cn-zhangjiakou.aliyuncs.com/sshirtmtm/', SUBSTRING_INDEX( s1.filePath, '/',- 1 ) ) AS picurl \n" +
                "FROM\n" +
                "	(\n" +
                "	SELECT\n" +
                "		a.material_id AS \"id\",\n" +
                "		a.material_file_id AS \"fileId\",\n" +
                "		a.material_name_cn AS \"materialNameCn\",\n" +
                "		a.material_name_en AS \"materialNameEn\",\n" +
                "		a.material_code AS \"materialCode\",\n" +
                "		a.material_use_type AS \"materialUseType\",\n" +
                "		a.material_type_cd AS \"materialTypeCd\",\n" +
                "		a.material_composition AS \"materialComposition\",\n" +
                "		a.material_spec AS \"materialSpec\",\n" +
                "		a.material_unit_cd AS \"materialUnitCd\",\n" +
                "		a.material_year AS \"materialYear\",\n" +
                "		a.material_color AS \"materialColor\",\n" +
                "		a.material_season AS \"materialSeason\",\n" +
                "		a.remarks AS \"remarks\",\n" +
                "		a.material_category AS \"materialStyleCategory\",\n" +
                "		a.model_filepath AS \"modelFilepath\",\n" +
                "	CASE\n" +
                "			\n" +
                "			WHEN file.FILE_PATH IS NOT NULL \n" +
                "			AND file.FILE_PATH != '' THEN\n" +
                "				REPLACE ( CONCAT( file.FILE_PATH, file.UPLOAD_FILE ), 'fragsmart-erp', 'fragsmart-mtm' ) ELSE REPLACE ( CONCAT( upload_file.FILE_PATH, upload_file.UPLOAD_FILE ), 'fragsmart-erp', 'fragsmart-mtm' ) \n" +
                "				END AS filePath,\n" +
                "			a_login_user_p.LAST_NAME AS \"createby.lastName\",\n" +
                "			a_login_user_p.FIRST_NAME AS \"createby.firstName\",\n" +
                "			IFNULL( s.material_sale_price, 0 ) AS \"materialPrice.materialSalePrice\",\n" +
                "			IFNULL( inventory_material.MATERIAL_QUANTITY, 0 ) AS \"inventoryMaterial.materialQuantity\",\n" +
                "			a.MATERIAL_LEVEL AS \"materialLevel\",\n" +
                "			a.MATERIAL_STYLE AS \"materialStyle\",\n" +
                "			a.MATERIAL_WEAVE AS \"materialWeave\",\n" +
                "		CASE\n" +
                "				\n" +
                "				WHEN adp.ITEM_VALUE = \"A_150\" THEN\n" +
                "				150 \n" +
                "				WHEN adp.ITEM_VALUE = \"B_180\" THEN\n" +
                "				180 \n" +
                "				WHEN adp.ITEM_VALUE = \"C_240\" THEN\n" +
                "				240 \n" +
                "				WHEN adp.ITEM_VALUE = \"D_320\" THEN\n" +
                "				320 ELSE 0 \n" +
                "			END AS \"materiaFacPrice\" \n" +
                "		FROM\n" +
                "			i_material_p a\n" +
                "			LEFT JOIN a_shop_material_r ar ON a.MATERIAL_ID = ar.MATERIAL_ID\n" +
                "			LEFT JOIN a_login_user_p a_login_user_p ON a_login_user_p.login_user_id = a.create_user\n" +
                "			LEFT JOIN i_material_price_s s ON s.material_id = a.material_id \n" +
                "			AND s.SHOP_ID = 18\n" +
                "			INNER JOIN i_inventory_material_p inventory_material ON inventory_material.material_id = a.material_id\n" +
                "			LEFT JOIN w_upload_file_p file ON a.material_file_id = file.FILE_ID \n" +
                "			AND file.FTP_FILE = \"/material\"\n" +
                "			LEFT JOIN a_upload_file_p upload_file ON upload_file.FILE_ID = a.MATERIAL_FILE_ID \n" +
                "			AND upload_file.FILE_KBN = 0 \n" +
                "			AND upload_file.FTP_FILE = \"/material\"\n" +
                "			LEFT JOIN a_dict_p adp ON a.MATERIAL_LEVEL = CONCAT( adp.ITEM_CD, \"-\", adp.ITEM_VALUE ) \n" +
                "		WHERE\n" +
                "			a.delete_flag = 0 \n" +
                "			AND a.material_category IN ( 'MATERIAL_CATEGORY-Fabric', 'MATERIAL_CATEGORY-ButtonL', 'MATERIAL_CATEGORY-Suit_Fabric', 'MATERIAL_CATEGORY-Suit_Material' ) \n" +
                "			AND ar.SHOP_ID = 18  AND a.MATERIAL_ID ='" + id + "'\n" +
                //"			AND ( a.material_name_cn LIKE '%" + mlname + "%' OR a.material_code LIKE '%" + mlname + "%' ) \n" +
                "           AND a.material_category ='MATERIAL_CATEGORY-Fabric'" +
                "		ORDER BY\n" +
                "			a.MATERIAL_CATEGORY,\n" +
                "			a.MATERIAL_ID \n" +
                "	) AS s1";
            return SQLmtm.GetDataRow(sql);
        }
        /// <summary>
        ///  面料打印log信息写入
        /// </summary>
        /// <param name="id"></param>
        public static void InsertMianLiaoLog(String id)
        {
            DataRow dr = SQLmtm.GetDataRow("SELECT\n" +
 "	* \n" +
 "FROM\n" +
 "	t_material_log \n" +
 "WHERE\n" +
 "	material_id = "+id+";");
            if(dr==null)
            {
                SQLmtm.DoInsert("t_material_log", new string[] { "material_id", "enable_flag" }, new string[] { id, "1" });
            }
        }
        /// <summary>
        /// 面料log信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetMianLiaoLogDT()
        {
            String sql = "SELECT\n" +
"	* \n" +
"FROM\n" +
"	t_material_log AS ml\n" +
"	LEFT JOIN i_material_p AS ip ON ml.material_id = ip.MATERIAL_ID WHERE ml.enable_flag='1';";
            DataTable dt = SQLmtm.GetDataTable(sql);
            return dt;
        }
        /// <summary>
        /// 更改面料
        /// </summary>
        /// <param name="orderid"></param>
        /// <param name="fabricid"></param>
        public static void ChangeFabric(String styleid,String fabricid)
        {
            SQLmtm.DoUpdate("s_style_p", new string[] { "SYTLE_FABRIC_ID" }, new string[] { fabricid }, new string[] { "SYS_STYLE_ID" }, new string[] { styleid });
        }
        /// <summary>
        /// 样衣订单id查样衣面料
        /// </summary>
        /// <param name="Orderid"></param>
        /// <returns></returns>
        public static DataTable GetDTBySampleOrderID(String orderid)
        {
            String sql = "SELECT\n" +
"	oop.ORDER_ID,\n" +
"	oop.STYLE_ID,\n" +
"	imp.MATERIAL_ID,\n" +
"	imp.MATERIAL_CODE,\n" +
"	imp.MATERIAL_NAME_CN,\n" +
"	imp.MATERIAL_OTHER1,\n" +
"	imp.MATERIAL_COMPOSITION \n" +
"FROM\n" +
"	o_order_p oop\n" +
"	LEFT JOIN s_style_p ssp ON oop.STYLE_ID = ssp.SYS_STYLE_ID\n" +
"	LEFT JOIN i_material_p imp ON ssp.SYTLE_FABRIC_ID = imp.MATERIAL_ID \n" +
"WHERE\n" +
"	oop.ORDER_ID = '"+ orderid + "'";
            return SQLmtm.GetDataTable(sql);
        }
        /// <summary>
        /// 样衣订单id查样衣面料DR
        /// </summary>
        /// <param name="orderid"></param>
        /// <returns></returns>
        public static DataRow GetDRBySampleOrderID(String orderid)
        {
            String sql = "SELECT\n" +
"	oop.ORDER_ID,\n" +
"	oop.STYLE_ID,\n" +
"	imp.MATERIAL_ID,\n" +
"	imp.MATERIAL_CODE,\n" +
"	imp.MATERIAL_NAME_CN,\n" +
"	imp.MATERIAL_OTHER1,\n" +
"	imp.MATERIAL_COMPOSITION \n" +
"FROM\n" +
"	o_order_p oop\n" +
"	LEFT JOIN s_style_p ssp ON oop.STYLE_ID = ssp.SYS_STYLE_ID\n" +
"	LEFT JOIN i_material_p imp ON ssp.SYTLE_FABRIC_ID = imp.MATERIAL_ID \n" +
"WHERE\n" +
"	oop.ORDER_ID = '" + orderid + "'";
            return SQLmtm.GetDataRow(sql);
        }
        /// <summary>
        /// 保存样衣面料任务
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public static Boolean SaveInMTM样衣面料任务(面料样品DTO dto)
        {
            if (SQLmtm.GetDataRow("SELECT * FROM `t_sample_material` WHERE ORDER_ID='" + dto.ORDER_ID + "'") == null)
            {
                try
                {
                    String sql = "INSERT INTO `fragsmart-mtm`.`t_sample_material`(`ORDER_ID`, `MATERIAL_ID`, `UNIT_CONSUMPTION`, `SAMPLE_PIC`, `REMARK`) VALUES ('" + dto.ORDER_ID + "', '" + dto.MATERIAL_ID + "', '" + dto.UNIT_CONSUMPTION + "', '" + dto.imageIOStream + "', '" + dto.REMARK + "');";
                    SQLmtm.ExecuteSql(sql);
                    return true;
                }
                catch
                {
                    return false;
                }
            }
            else
            {
                try
                {
                    String sql = "UPDATE `fragsmart-mtm`.`t_sample_material` SET `MATERIAL_ID` = '" + dto.MATERIAL_ID + "', `UNIT_CONSUMPTION` = '" + dto.UNIT_CONSUMPTION + "', `SAMPLE_PIC` = '" + dto.imageIOStream + "', `REMARK` = '" + dto.REMARK + "' WHERE `ORDER_ID` = '" + dto.ORDER_ID + "';";
                    SQLmtm.ExecuteSql(sql);
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }
        /// <summary>
        /// 根据订单id查询面料任务 为面料样衣dto构造正准备
        /// </summary>
        /// <param name="orderid"></param>
        /// <returns></returns>
        public static DataRow GetDRFromt_sample_material(String orderid)
        {
            String sql = "SELECT\n" +
"	tsm.ORDER_ID,\n" +
"	tsm.MATERIAL_ID,\n" +
"	tsm.UNIT_CONSUMPTION,\n" +
"	tsm.SAMPLE_PIC,\n" +
"	tsm.REMARK \n" +
"FROM\n" +
"	t_sample_material tsm \n" +
"WHERE\n" +
"	tsm.ORDER_ID = '" + orderid + "';";
            return SQLmtm.GetDataRow(sql);
        }
        /// <summary>
        /// 上传辅料配料文档
        /// </summary>
        /// <param name="str"></param>
        /// <param name="orderid"></param>
        public static void Updata辅配料文档(String str,String orderid)
        {
            try
            {
                SQLmtm.DoUpdate("t_remark", new string[] { "remark" }, new string[] { str }, new string[] { "order_id", "template_id" }, new string[] { orderid, ((int)Enum样衣子任务.面料任务).ToString() });
            }
            catch
            {
            }
        }
        /// <summary>
        /// 添加样衣物料信息
        /// </summary>
        /// <param name="list"></param>
        /// <param name="dto面料样品"></param>
        /// <returns></returns>
        public static List<面料信息dto> Add样衣物料信息(List<面料信息dto> list ,面料样品DTO dto面料样品)
        {
            List<面料信息dto> lst = list;
            foreach(面料信息dto dto in lst)
            {
                dto.MATERIAL_ID = dto面料样品.MATERIAL_ID;
                dto.UNIT_CONSUMPTION = dto面料样品.UNIT_CONSUMPTION;
                dto.REMARK = dto面料样品.REMARK;
            }
            return lst;
        }
        /// <summary>
        /// 根据code查询面料信息
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public static 面料DTO Get面料DtoByCD(String code)
        {
            String sql = "SELECT\n" +
                    "	id,\n" +
                    "	fileId,\n" +
                    "	materialNameCn,\n" +
                    "	materialNameEn,\n" +
                    "	materialCode,\n" +
                    "	materialUseType,\n" +
                    "	materialTypeCd,\n" +
                    "	materialComposition,\n" +
                    "	materialSpec,\n" +
                    "	materialUnitCd,\n" +
                    "	materialYear,\n" +
                    "	materialColor,\n" +
                    "	materialSeason,\n" +
                    "	remarks,\n" +
                    "	materialStyleCategory,\n" +
                    "	modelFilepath,\n" +
                    "	filePath,\n" +
                    "	createby_lastName,\n" +
                    "	createby_firstName,\n" +
                    "	materialPrice_materialSalePrice,\n" +
                    "	inventoryMaterial_materialQuantity,\n" +
                    "	materialLevel,\n" +
                    "	materialStyle,\n" +
                    "	materialWeave,\n" +
                    "	materiaFacPrice,\n" +
                    "	mianliao,\n" +
                    "	picn,\n" +
                    "	picurl \n" +
                    "FROM\n" +
                    "	V_MATERIAL_CATEGORY_Fabric \n" +
                    "WHERE\n" +
                    "	materialCode='"+code+"'";
            return new 面料DTO(SQLmtm.GetDataRow(sql));
        }

        public static 面料DTO Get面料DtoByCD新(String code)
        {
            String sql = "SELECT \n" +
"  MATERIAL_ID AS id,\n" +
"	MATERIAL_FILE_ID AS fileId,\n" +
"	MATERIAL_NAME_CN AS materialNameCn,\n" +
"	MATERIAL_NAME_EN AS materialNameEn,\n" +
"	MATERIAL_CODE AS materialCode,\n" +
"	MATERIAL_USE_TYPE AS materialUseType,\n" +
"	MATERIAL_TYPE_CD AS materialTypeCd,\n" +
"	MATERIAL_COMPOSITION AS materialComposition,\n" +
"	MATERIAL_SPEC AS materialSpec,\n" +
"	MATERIAL_UNIT_CD AS materialUnitCd,\n" +
"	MATERIAL_YEAR AS materialYear,\n" +
"	MATERIAL_COLOR AS materialColor,\n" +
"	MATERIAL_SEASON AS materialSeason,\n" +
"	REMARKS AS remarks,\n" +
"	MATERIAL_STYLE_CATEGORY AS materialStyleCategory,\n" +
"	MODEL_FILEPATH AS modelFilepath,\n" +
"	'' AS filePath,\n" +
"	'' AS createby_lastName,\n" +
"	'' AS createby_firstName,\n" +
"	''  AS materialPrice_materialSalePrice,\n" +
"	'' AS inventoryMaterial_materialQuantity,\n" +
"	'' AS materialLevel,\n" +
"	'' AS materialStyle,\n" +
"	'' AS materialWeave,\n" +
"	'' AS materiaFacPrice,\n" +
"	'' AS mianliao,\n" +
"	'' AS picn,\n" +
"	'' AS picurl \n" +
"FROM\n" +
"	i_material_p \n" +
"WHERE\n" +
"	MATERIAL_CODE = '" + code + "';";
            return new 面料DTO(SQLmtm.GetDataRow(sql));
        }
        /// <summary>
        /// 美国坚山水洗取数据
        /// </summary>
        /// <param name="barCode"></param>
        /// <returns></returns>
        public static DataRow Get美国坚山水洗数据(String barCode)
        {
            String sql = "SELECT\n" +
                    "	o_order_p.ORDER_NO,\n" +
                    "	o_order_p.CUSTOM_NAME,\n" +
                    "	i_material_p.MATERIAL_CODE,\n" +
                    "	i_material_p.MATERIAL_COMPOSITION \n" +
                    "FROM\n" +
                    "	o_order_p\n" +
                    "	LEFT JOIN s_style_p ON o_order_p.STYLE_ID = s_style_p.SYS_STYLE_ID\n" +
                    "	LEFT JOIN i_material_p ON s_style_p.SYTLE_FABRIC_ID = i_material_p.MATERIAL_ID \n" +
                    "WHERE\n" +
                    "	o_order_p.ORDER_NO LIKE '%" + barCode + "%';";
            return SQLmtm.GetDataRow(sql);
        }
    }
}
